package com.hellogood.yaohuo.server.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.hellogood.yaohuo.server.db.ConnectionPool;
import com.hellogood.yaohuo.server.db.DBHelper;
import com.hellogood.yaohuo.server.mgr.factory.MgrFactory;
import com.hellogood.yaohuo.shared.data.CallDTO;
import com.hellogood.yaohuo.shared.entity.YKDetail;
import com.hellogood.yaohuo.shared.entity.YKForm;
import com.hellogood.yaohuo.shared.exception.DatabaseException;
import com.hellogood.yaohuo.shared.vo.MainFrame;

public class YKFormDAO extends DBHelper {
	Logger log = Logger.getLogger(this.getClass());

	@Override
	public List<?> initializeObject(ResultSet rs) throws SQLException {
		List<YKForm> formList = new ArrayList<YKForm>();
		if (rs != null) {
			while (rs.next()) {
				YKForm form = new YKForm();
				form.setYkBillNo(rs.getString("ykBillNo"));
				form.setYkDate(rs.getString("ykDate"));
				form.setOutOrgCode(rs.getString("outOrgCode"));
				form.setOutOrgName(rs.getString("outOrgName"));
				form.setInOrgCode(rs.getString("inOrgCode"));
				form.setInOrgName(rs.getString("inOrgName"));
				form.setCounts(rs.getInt("counts"));
				form.setCost(rs.getBigDecimal("cost"));
				form.setNetCost(rs.getBigDecimal("netCost"));
				form.setTotal(rs.getBigDecimal("total"));
				form.setStaffCode(rs.getString("staffCode"));
				form.setStaffName(rs.getString("staffName"));
				form.setTimeMark(rs.getString("timeMark"));
				form.setRzDate(rs.getString("rzDate"));
				form.setRemark(rs.getString("remark"));
				formList.add(form);
			}
		}
		return formList;
	}

	public String getLastYKBillNo(String billNo) {
		String sql = "SELECT top 1 * FROM ykForm WHERE ykBillNo LIKE '" + billNo + "%' ORDER BY ykBillNo desc";
		List<?> list = super.query(sql);
		if (list == null || list.isEmpty()) {
			return null;
		}
		YKForm form = (YKForm) list.get(0);
		return form.getYkBillNo();

	}

	public String createYiku(YKForm form, boolean isModify) {
		String result = "";
		List<String> sqls = new ArrayList<String>();
		if (isModify) {
			String sql = "DELETE FROM YKForm WHERE ykBillNo = '" + form.getYkBillNo() + "'";
			sqls.add(sql);
			sql = "DELETE FROM YKDetail WHERE ykBillNo = '" + form.getYkBillNo() + "'";
			sqls.add(sql);
		}
		String sql = "INSERT INTO YKForm(ykBillNo,ykDate,outOrgCode,outOrgName,inOrgCode,inOrgName,counts,cost,netCost,total,staffCode,"
				+ "staffName,rzDate,remark) VALUES('"
				+ form.getYkBillNo()
				+ "',CONVERT(varchar(100), GETDATE(), 23),'"
				+ form.getOutOrgCode()
				+ "','"
				+ form.getOutOrgName()
				+ "','"
				+ form.getInOrgCode()
				+ "','"
				+ form.getInOrgName()
				+ "',"
				+ form.getCounts()
				+ ","
				+ form.getCost()
				+ ","
				+ form.getNetCost()
				+ ","
				+ form.getTotal()
				+ ",'"
				+ form.getStaffCode()
				+ "','"
				+ form.getStaffName()
				+ "','"
				+ form.getRzDate()
				+ "','"
				+ form.getRemark()
				+ "')";
		sqls.add(sql);

		List<YKDetail> list = form.getList();
		if (list != null && (!list.isEmpty())) {
			for (YKDetail d : list) {
				sql = "INSERT INTO YKDetail(ykBillNo,pluCode,pluName,orgCode,barCode,depCode,depName,bra,color,csize,jPrice,netJPrice,price,"
						+ "counts,cost,netCost,total,rzDate,remark) VALUES('"
						+ d.getYkBillNo()
						+ "','"
						+ d.getPluCode()
						+ "','"
						+ d.getPluName()
						+ "','"
						+ d.getOrgCode()
						+ "','"
						+ d.getBarCode()
						+ "','"
						+ d.getDepCode()
						+ "','"
						+ d.getDepName()
						+ "','"
						+ d.getBra()
						+ "','"
						+ d.getColor()
						+ "','"
						+ d.getSize()
						+ "',"
						+ d.getjPrice()
						+ ","
						+ d.getNetJPrice()
						+ ","
						+ d.getPrice()
						+ ","
						+ d.getCounts()
						+ ","
						+ d.getCost()
						+ ","
						+ d.getCost() + "," + d.getTotal() + ",'" + d.getRzDate() + "','" + d.getRemark() + "')";
				sqls.add(sql);
			}
		}
		try {
			executeBatch(sqls);
		} catch (DatabaseException e) {
			e.printStackTrace();
			log.error(e.getMessage());
			return e.getMessage();
		}
		return result;
	}

	@SuppressWarnings("unchecked")
	public List<YKForm> searchYKFormByQuyu(String quyuCode, String startDate, String endDate, String rz) {
		String userCode = MainFrame.currentUser.getStaffCode();
		String orgs = "";
		if (quyuCode == null) {
			orgs = MgrFactory.getInstance().createOrgMgr().getOrgCodeByUser(userCode);
		} else {
			orgs = MgrFactory.getInstance().createOrgMgr().getOrgCodeByUserQuyu(userCode, quyuCode);
		}

		String sql = "SELECT * FROM YKForm WHERE OutOrgCode IN (?) AND cast(ykDate as char(10)) >= CONVERT(VARCHAR(10),CONVERT(DATETIME,?),120) and cast(ykDate as char(10)) <= CONVERT(VARCHAR(10),CONVERT(DATETIME,?),120)";
		if (rz.equals("已入账")) {
			sql = sql + " AND rzDate <> ''";
		} else if (rz.equals("未入账")) {
			sql = sql + " AND rzDate = ''";
		}
		Object[] args = new Object[] { orgs, startDate, endDate };
		List<?> list = super.query(sql, args);
		return (List<YKForm>) list;
	}

	@SuppressWarnings("unchecked")
	public List<YKForm> searchYKForm(String orgCode, String startDate, String endDate, String rz) {
		String sql = "SELECT * FROM YKForm WHERE OrgCode = ? AND cast(YKDate as char(10)) >= CONVERT(VARCHAR(10),CONVERT(DATETIME,?),120) and cast(YKDate as char(10)) <= CONVERT(VARCHAR(10),CONVERT(DATETIME,?),120)";
		if (rz.equals("已入账")) {
			sql = sql + " AND rzDate <> ''";
		} else if (rz.equals("未入账")) {
			sql = sql + " AND rzDate = ''";
		}
		Object[] args = new Object[] { orgCode, startDate, endDate };
		List<?> list = super.query(sql, args);
		return (List<YKForm>) list;
	}

	public YKForm getFormByBillNo(String billNo) {
		String sql = "SELECT * FROM YKForm WHERE ykBillNo = ?";
		Object[] args = new Object[] { billNo };
		List<?> list = super.query(sql, args);
		if (list != null && (!list.isEmpty())) {
			return (YKForm) list.get(0);
		}
		return null;
	}

	public String deleteYiku(String billNo) {
		String result = "";
		List<String> sqls = new ArrayList<String>();

		String sql = "DELETE FROM YKForm WHERE ykBillNo = '" + billNo + "'";
		sqls.add(sql);
		sql = "DELETE FROM YKDetail WHERE ykBillNo = '" + billNo + "'";
		sqls.add(sql);
		try {
			executeBatch(sqls);
		} catch (DatabaseException e) {
			e.printStackTrace();
			log.error(e.getMessage());
			return e.getMessage();
		}
		return result;
	}

	public void rz(String bill) throws DatabaseException {
		CallDTO dto = new CallDTO();
		String sql = "UPDATE ykForm SET rzDate WHERE ykBillNo = '" + bill + "'";
		String call = "{CALL ykrz(?,?)}";
		dto.setBill(bill);
		dto.setCall(call);
		dto.setSql(sql);
		dto.setHxdbName(ConnectionPool.hxDbName);
		super.callProcedure(dto);
	}
}